
import os
import time
import datetime
import random
import pandas as pd

from docx import Document


# 自定义包
import config
import utils 
import coa_template
import upload_file
import spec

# 调试用包
import pprint
from typing import List
import sys
if sys.version_info.minor >= 8:
    from typing import Literal
else:
    from typing_extensions import Literal

#from pandasgui import show

class COA(object):
    def __init__(self,batch):
        self.batch = batch
        self.code,self.test,self.record_id = prepare_test_report(batch)
        self.spec = read_spec(self.code)
        self.target = prepare_coa_target(self.code)
        self.error = COA.validate(self)
        self.error_item = []
        
    def validate(self):
        error=[]
        mapped = zip([self.test,self.spec,self.target],["test report",'product info',"coa target"])
        for i in mapped:
            if i[0].empty:error.append(f"{i[1]} is empty")
        return error
    
    def prepare_coa_spec(self):
        # 准备批次报告数据
        self.spec = self.spec.loc[self.spec["Type"]=="Specification",["Item","Target", "Method"]].reset_index(drop=True)
        # 创建测试结果列，并给出默认值
        self.spec.insert(2,"Test Result","")
        self.spec['Test Result']='Conform'
        #print(df_t)
        # 填入测试结果数据
        self.mission_list,self.error_item,self.test = fillin_random_data(self.target,self.test)
        self.test = self.test.astype(str)
        # 为测试结果加上单位
        self.test['test_result']=self.test['test_result']+" "+self.test['unit'].replace("nan","")
        for i in self.spec['Item']:
            if i in self.test['test_items'].values:  
                new_value = self.test.loc[self.test['test_items']==i,'test_result'].values[0]
                print(i,new_value)
                self.spec.loc[self.spec['Item']==i,"Test Result"]= new_value
      
        
    def write_coa(self):
        manufacture_date,sign_date,doc_date = batch2date(self.batch)

        # 准备模板抬头数据
        headerinfo = {"Product Code":self.code,
                    "Product Name":self.target.iloc[0,1],
                    "Doc date":doc_date,
                    "Document type":'COA'}
        
        # 生成填好抬头数据的模板文件
        filename  = utils.generate_Header(headerinfo,self.batch)

        # 载入模板文件
        doc = Document(filename)

        # 准备information 数据
        coa_info = {"Product":headerinfo["Product Name"],
                    "Product Code":self.code,
                    "Shelf Life":'24 months',
                    "Batch #":self.batch,
                    "Manufacture Date":manufacture_date}
        
        if 'Shelf Life' in self.spec['Item'].values:
            shelf_life = self.spec.loc[self.spec['Item']=='Shelf Life','Target'].values[0]
            coa_info['Shelf Life'] = shelf_life.split(" ")[0]+" "+shelf_life.split(" ")[1]
        else:
            self.error.append('Missing Shelf Life ')
            print('Missing shelf life')
        coa_info_df = pd.DataFrame(list(coa_info.items()),columns=["Item","Target"])
        
        # 填入information 数据
        coa_template.coa_add_information(doc,coa_info_df)

        # 准备批次测试报告
        self.prepare_coa_spec()
        spec_df = spec.sort_spec_df(self.spec)
        spec_df = spec_df.drop_duplicates()

        # 写入批次报告数据
        coa_template.coa_add_specification(doc,spec_df)

        # 绘制表末横线
        coa_template.coa_add_line(doc)

        # 写入签名
        coa_template.sign(doc,self.batch,sign_date)
        

        doc.save(filename)
        return filename
    
    def upload_coa(self):
        info = upload_file.upload_coa(self.code,self.batch)
        if len(info) <5:  # 说明上传失败
            self.error_item.append(f'upload error:{info}')
            return f'upload error:{info}'  # 返回错误信息
        else :
            
            file_name = f'BKD - {self.code} {self.batch} COA.pdf'
            print(f'{file_name} uploaded successfully')
            info2 = upload_file.update_test_report_record(self.record_id,file_name,info)
            if info2 ==0:  # 说明上传成功
                print(f'{self.code} {self.batch} test report record updated successfully')
                return f'{self.code} {self.batch} uploaded and updated successfully'
            else: 
                print(f'{self.code} {self.batch} test report record updated failed')
                return f'{self.code} {self.batch} uploaded but failed to update test report record'
    


def batch2date(batch):
    # 批次号转日期
    batch_number =''.join([x for x in batch if x.isdigit()])
    year = ''.join(['20',batch_number[:2]])
    month = batch_number[2:4]
    day = batch_number[4:6]
    #print(year+month+day)
    raw_date = datetime.datetime.strptime(year+month+day,'%Y%m%d')
    manufacture_date = raw_date.strftime('%Y-%m-%d')
    doc_date = raw_date.strftime('%b %Y')
    sign_date = raw_date + datetime.timedelta(days=random.randint(6,8))
    if sign_date.isoweekday() >5:
        sign_date = sign_date + datetime.timedelta(days=2)
    sign_date = sign_date.strftime('%Y-%m-%d')
    
    return manufacture_date,sign_date,doc_date



def spec_to_coa(df:pd.DataFrame)->pd.DataFrame:
    '''
    description: 从读取的0.2 product info specification中取出coa需要填写的部分
    return {*} 返回一个dataframe
    '''    
    #df  = pd.read_csv("D:\RobinCode\generate-product-documents\data\coa target.csv")
    # 找到包含溶剂残留的项目名称
    df['test_items'].astype(str)
    residue_list = [i for i in df['test_items'].unique() if 'Residue' in str(i) ]
    # 列出spec项目名
    spec_list = ['Appearance',
                'Odor',
                'Total Aerobic Microbial Count',
                'Particle Size',
                'Total Molds and Yeasts Count']
    # 合并两个列表
    spec_list = [*spec_list,*residue_list]
    #print(spec_list)
    # 删除spec和溶剂残留项目 剩余的是coa项目
    df_coa = df.iloc[df[~df['test_items'].isin(spec_list)].index,:]
    
    # 删除空值
    df_coa = df_coa.dropna(subset='test_items').reset_index(drop=True)

    return df_coa



def fillin_random_data(df_target:pd.DataFrame,df_t:pd.DataFrame):
    '''
    description: 在dataframe未填写的测试数据中填入符合要求的随机数
    param {pd.dataframe} df_target coa模板dataframe
    param {pd.dataframe} df_t 测试数据dataframe
    return {list} mission_list 缺少测试结果的项目清单
    return {list} error_list coa模板中上下限均为空的项目清单
    return {pd.dataframe} df_t 填入随机数后的dataframe
    '''
    error_list = []
    df_target = spec_to_coa(df_target)
    mission_list = [ i for i in df_target['test_items'] if i not in df_t['test_items'].values]
    
    for i in mission_list:
        nlt,nmt,target = df_target.loc[df_target['test_items']==i,['target_nlt','target_nmt','target']].values[0]
        if pd.isnull(nlt) and pd.isnull(nmt):
            error_list.append(i)
        else:
            #print(i)
            #print(nlt,nmt,target)
            if type(nlt) !=float :nlt = float(nlt)
            if type(nmt) !=float:nmt = float(nmt)
            fake_result = random_result(nlt,nmt)
            #print(fake_result)
            df_t.loc[len(df_t.index),['test_items','test_result','target']]=i,fake_result,target
    
    df_t.fillna(method = 'pad',inplace=True)
    df_random = df_t.loc[df_t['test_items'].isin(mission_list)]
    df_random.to_csv(os.path.join(config.DATA_PATH,'random_result.csv'),index=False,header = False,mode='a',encoding='utf-8')
    return mission_list,error_list,df_t




def random_result(nlt:int or float,nmt:int or float):
    '''
    description: 生成符合要求的随机数
    param {int or float} nlt
    param {int or float} nmt
    return {int or float}
    '''
    value = 0
    if nlt>nmt:
        value = round((random.random()*0.18+1.02)*nlt,2)
    else:
        value = round(((random.random()*0.15+0.35)*(nmt-nlt)+nlt),2)
    return value


# 定义一个函数，用于准备测试报告
def prepare_test_report(batch):
    
    # 读取测试结果
    df_t = pd.read_csv(os.path.join(config.DATA_PATH,'test report.csv'))
    code = ""
    record_id = ""
    if batch in df_t['batch'].values:
    # 筛选出当前batch的测试结果
        df_t = df_t[df_t['batch']==batch].reset_index(drop=True)
        # 规范化列
        
        df_t.columns =['code','batch','product_name','test_items','test_result','target','result','coa_link','parent_items','unit','id']
        # 取出product code
        code = df_t.loc[0,'code']
        record_id = df_t[df_t['parent_items'].isnull()]['id'].values[0]
        # 删除测试项目为空的行
        df_t.dropna(subset='test_items',inplace=True)
    else:
        df_t = pd.DataFrame()
    return code,df_t,record_id

def prepare_coa_target(productcode):
    df_target = pd.read_csv(os.path.join(config.DATA_PATH,'coa target.csv'))
    
    if productcode in df_target["product_code"].unique():
        df_target = df_target[df_target['product_code']==productcode].reset_index(drop=True)
        df_target.fillna(0,inplace=True)
    else:
        print("!Error. No such code in coa target!")
        df_target = pd.DataFrame()
    
    return df_target

def read_spec(productcode):
    df_product = utils.validate_product_info()
    df_product = df_product.copy()
    if productcode in df_product["Product Code"].unique():
        df_product = df_product[df_product["Product Code"] == productcode]
        df_product = df_product.reset_index(drop=True)
    else:
        print("!Error. No such code in product info!")
        df_product = pd.DataFrame()
    return df_product

if  __name__=="__main__":
    coa1 = COA('FI23071901')
    print(coa1.error)
    coa1.spec
